/****** Object:  UserDefinedFunction [dbo].[f_timekeeping_truncate_text]    Script Date: 1/31/2019 10:12:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
Create  FUNCTION [dbo].[f_timekeeping_truncate_text]
(
	-- Add the parameters for the function here
	@p_input nvarchar(4000)
	,@p_length int
)
RETURNS varchar(500)
AS
BEGIN
	
	DECLARE @w_answer nvarchar(500)
			,@w_workString nvarchar(500);

	if (len(@p_input) <= @p_length) begin
		set @w_answer = @p_input;
	end else begin
		
		set @w_workString = SUBSTRING(@p_input,1,@p_length -3);

		set @w_answer =(@w_workString + '...')
	
	end

	RETURN @w_answer;

END


GO

/****** Object:  Table [dbo].[LanguageLabels]    Script Date: 6/29/2019 12:31:53 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LanguageLabels](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[LanguageID] [nvarchar](20) NULL,
	[LabelID] [int] NULL,
	[LabelContent] [nvarchar](256) NULL,
	[UpdateDate] [datetime] NULL,
 CONSTRAINT [PK_LanguageLabels] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LanguageLabels] ADD  CONSTRAINT [DF_LanguageLabels_UpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]
GO


/****** Object:  Table [dbo].[TimeKeeping_Activity_Code_Master]    Script Date: 1/31/2019 10:12:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Activity_Code_Master](
	[AC_CVT_ID] [int] IDENTITY(8000,1) NOT NULL,
	[AC_CGL_ID] [varchar](1) NULL,
	[AC_NAME] [varchar](100) NULL,
	[AC_TEXT] [varchar](100) NULL,
	[AC_DESCRIPTON] [varchar](256) NULL,
	[AC_INTERFACE_TOKEN] [varchar](50) NULL,
	[AC_INTERFACE_STATUS] [varchar](1) NOT NULL,
	[AC_PARENT_AC] [int] NULL,
	[AC_DISPLAY_SEQ] [int] NULL,
	[AC_CGL_PROCESS] [varchar](50) NULL,
	[AC_TIMEKEEPING_STATUS] [varchar](1) NULL,
	[AC_UPDATED_DATE] [datetime] NULL,
	[AC_LANGUAGE_ID] [varchar](20) NULL,
	[AC_TEXT_ES] [varchar](100) NULL,
	[AC_TEXT_PR] [varchar](100) NULL,
	[AC_INSERT_DATE] [datetime] NULL,
 CONSTRAINT [PK_TK_Activity_Code] PRIMARY KEY CLUSTERED 
(
	[AC_CVT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Date_Status]    Script Date: 1/31/2019 10:12:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Date_Status](
	[DS_DATE] [date] NOT NULL,
	[DS_PLANT_ID] [int] NOT NULL,
	[DS_EMPLOYEE_ID] [int] NOT NULL,
	[DS_INTERFACE_STATUS] [varchar](1) NOT NULL,
 CONSTRAINT [PK_DS_DATE_PLANT] PRIMARY KEY CLUSTERED 
(
	[DS_DATE] ASC,
	[DS_PLANT_ID] ASC,
	[DS_EMPLOYEE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Date_Table]    Script Date: 1/31/2019 10:12:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Date_Table](
	[DT_DATE] [date] NOT NULL,
 CONSTRAINT [PK_DT_DATE] PRIMARY KEY CLUSTERED 
(
	[DT_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_DateActivity_Status]    Script Date: 1/31/2019 10:12:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_DateActivity_Status](
	[AS_DATE] [date] NOT NULL,
	[AS_PLANT_ID] [int] NOT NULL,
	[AS_ACTIVITY_CODE] [int] NOT NULL,
	[AS_INTERFACE_STATUS] [varchar](1) NOT NULL,
	[AS_INTERFACE_ERROR] [varchar](512) NULL,
	[AS_DATE_CREATED] [datetime] NULL,
 CONSTRAINT [PK_AS_DATEACTIVITY] PRIMARY KEY CLUSTERED 
(
	[AS_DATE] ASC,
	[AS_PLANT_ID] ASC,
	[AS_ACTIVITY_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Department_Master]    Script Date: 1/31/2019 10:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Department_Master](
	[DM_CVT_ID] [int] IDENTITY(5000,1) NOT NULL,
	[DM_CGL_ID] [int] NULL,
	[DM_NAME] [varchar](25) NULL,
	[DM_DESCRIPTION] [varchar](128) NULL,
	[DM_STATUS] [varchar](1) NULL,
 CONSTRAINT [PK_Department_ID] PRIMARY KEY CLUSTERED 
(
	[DM_CVT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Employee_Hours]    Script Date: 1/31/2019 10:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Employee_Hours](
	[EH_CVT_TRXN_ID] [int] IDENTITY(100000,1) NOT NULL,
	[EH_EMPLOYEE_ID] [int] NOT NULL,
	[EH_DATE_WORKED] [date] NOT NULL,
	[EH_ACTIVITY_CODE] [int] NOT NULL,
	[EH_HOURS_WORKED] [decimal](4, 2) NOT NULL,
	[EH_DATE_RECORDED] [date] NOT NULL,
	[EH_TIME_RECORDED] [time](7) NOT NULL,
	[EH_RECORDING_STATUS] [varchar](1) NOT NULL,
	[EH_INTERFACE_STATUS] [varchar](1) NOT NULL,
	[EH_INTERFACE_ERROR_CODE] [varchar](4) NULL,
	[EH_COMMENT] [varchar](50) NULL,
	[EH_PLANT_ID] [int] NULL,
 CONSTRAINT [PK_EH_TRXN_ID] PRIMARY KEY CLUSTERED 
(
	[EH_CVT_TRXN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Employee_Master]    Script Date: 1/31/2019 10:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Employee_Master](
	[EM_CVT_ID] [int] IDENTITY(1000,1) NOT NULL,
	[EM_CGL_ID] [int] NULL,
	[EM_FIRST_NAME] [varchar](50) NULL,
	[EM_LAST_NAME] [varchar](50) NULL,
	[EM_TITLE] [varchar](35) NULL,
	[EM_STATUS_CODE] [varchar](1) NULL,
	[EM_PLANT_ID] [int] NOT NULL,
	[EM_DEFAULT_SHIFT] [int] NULL,
	[EM_CURRENT_SHIFT] [int] NULL,
	[EM_DEPT_ID] [int] NULL,
	[EM_KEEPS_TIME] [varchar](10) NULL,
	[EM_IS_SUP] [varchar](1) NULL,
	[EM_IS_ADMIN] [varchar](1) NULL,
	[EM_USER_PROFILE] [varchar](20) NULL,
	[EM_DISPLAY_SEQ] [int] NULL,
 CONSTRAINT [PK_TK_Employee] PRIMARY KEY CLUSTERED 
(
	[EM_CVT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Plant_Activity_Codes]    Script Date: 1/31/2019 10:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Plant_Activity_Codes](
	[PAC_PLANT_ID] [int] NOT NULL,
	[PAC_ACTIVITY_ID] [int] NOT NULL,
	[PAC_CGL_ID] [varchar](1) NULL,
	[PAC_NAME] [varchar](100) NULL,
	[PAC_TEXT] [varchar](100) NULL,
	[PAC_DESCRIPTON] [varchar](256) NULL,
	[PAC_INTERFACE_TOKEN] [varchar](50) NULL,
	[PAC_INTERFACE_STATUS] [varchar](1) NOT NULL,
	[PAC_PARENT_AC] [int] NULL,
	[PAC_DISPLAY_SEQ] [int] NULL,
	[PAC_CGL_PROCESS] [varchar](50) NULL,
	[PAC_TIMEKEEPING_STATUS] [varchar](1) NULL,
	[PAC_UPDATED_DATE] [datetime] NULL,
	[PAC_LANGUAGE_ID] [varchar](20) NULL,
 CONSTRAINT [PK_TK_Plant_Activity_Code] PRIMARY KEY CLUSTERED 
(
	[PAC_ACTIVITY_ID] ASC,
	[PAC_PLANT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Plant_Master]    Script Date: 1/31/2019 10:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Plant_Master](
	[PM_CVT_ID] [int] IDENTITY(3000,1) NOT NULL,
	[PM_CGL_ID] [int] NULL,
	[PM_NAME] [nvarchar](100) NULL,
	[PM_CITY] [varchar](25) NULL,
	[PM_STATE_PROVINCE] [varchar](2) NULL,
	[PM_COUNTRY_CODE] [varchar](2) NULL,
	[PM_STATUS] [varchar](1) NULL,
	[PM_TOKEN] [varchar](50) NULL,
	[PM_IS_GRAIN] [varchar](1) NULL,
	[PM_IS_MILL] [varchar](1) NULL,
	[PM_Update_Date] [datetime] NULL,
 CONSTRAINT [PK_TK_Plant] PRIMARY KEY CLUSTERED 
(
	[PM_CVT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TimeKeeping_Shift_Master]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeKeeping_Shift_Master](
	[SM_CVT_ID] [int] IDENTITY(4000,1) NOT NULL,
	[SM_CGL_ID] [int] NULL,
	[SM_NAME] [varchar](25) NULL,
	[SM_DESCRIPTION] [varchar](128) NULL,
	[SM_START_TIME] [time](7) NULL,
	[SM_END_TIME] [time](7) NULL,
	[SM_STATUS] [varchar](1) NULL,
 CONSTRAINT [PK_TK_Shift] PRIMARY KEY CLUSTERED 
(
	[SM_CVT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[V_TimeKeeping_EMP_MASTER]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_EMP_MASTER]
AS
SELECT        EM_CVT_ID, EM_FIRST_NAME, EM_LAST_NAME, EM_STATUS_CODE, EM_PLANT_ID, EM_KEEPS_TIME, EM_IS_SUP, EM_IS_ADMIN, EM_USER_PROFILE
FROM            dbo.TimeKeeping_Employee_Master
GO
/****** Object:  View [dbo].[V_TimeKeeping_FRIDAY]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[V_TimeKeeping_FRIDAY]
AS
SELECT        CONVERT(VARCHAR(10), DATEADD(Day, - 3, GETDATE()), 120) AS FRIDAY

GO
/****** Object:  View [dbo].[V_TimeKeeping_FRIDAY_EMP]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_FRIDAY_EMP]
AS
SELECT        dbo.V_TimeKeeping_FRIDAY.FRIDAY, dbo.V_TimeKeeping_EMP_MASTER.EM_CVT_ID, dbo.V_TimeKeeping_EMP_MASTER.EM_PLANT_ID
FROM            dbo.V_TimeKeeping_FRIDAY CROSS JOIN
                         dbo.V_TimeKeeping_EMP_MASTER
WHERE        (dbo.V_TimeKeeping_EMP_MASTER.EM_KEEPS_TIME = 'Y')
GO
/****** Object:  View [dbo].[V_PLANT_ACTIVITY_CODES]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[V_PLANT_ACTIVITY_CODES]
AS
SELECT        dbo.TimeKeeping_Plant_Master.PM_CVT_ID, dbo.TimeKeeping_Plant_Master.PM_NAME, dbo.TimeKeeping_Activity_Code_Master.AC_CVT_ID AS AC_CVT_ID, COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_NAME, ''), 
                         dbo.TimeKeeping_Activity_Code_Master.AC_NAME, dbo.TimeKeeping_Plant_Activity_Codes.PAC_NAME) AS VAC_NAME, COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT, ''), dbo.TimeKeeping_Activity_Code_Master.AC_TEXT, 
                         dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT) AS VAC_TEXT, COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_DESCRIPTON, ''), dbo.TimeKeeping_Activity_Code_Master.AC_DESCRIPTON, 
                         dbo.TimeKeeping_Plant_Activity_Codes.PAC_DESCRIPTON) AS VAC_DESCRIPTION, dbo.TimeKeeping_Plant_Activity_Codes.PAC_INTERFACE_STATUS, dbo.TimeKeeping_Plant_Activity_Codes.PAC_DISPLAY_SEQ, 
                         dbo.TimeKeeping_Plant_Activity_Codes.PAC_TIMEKEEPING_STATUS, dbo.TimeKeeping_Activity_Code_Master.AC_INTERFACE_TOKEN, dbo.TimeKeeping_Activity_Code_Master.AC_CGL_PROCESS, dbo.TimeKeeping_Plant_Activity_Codes.PAC_LANGUAGE_ID, 
                         dbo.TimeKeeping_Activity_Code_Master.AC_LANGUAGE_ID
 
                         , COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT, ''), dbo.TimeKeeping_Activity_Code_Master.AC_TEXT_ES, dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT) AS VAC_TEXT_ES
                         , COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT, ''), dbo.TimeKeeping_Activity_Code_Master.AC_TEXT_PR, dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT) AS VAC_TEXT_PR
FROM            dbo.TimeKeeping_Plant_Master INNER JOIN
                         dbo.TimeKeeping_Plant_Activity_Codes ON dbo.TimeKeeping_Plant_Master.PM_CVT_ID = dbo.TimeKeeping_Plant_Activity_Codes.PAC_PLANT_ID INNER JOIN
                         dbo.TimeKeeping_Activity_Code_Master ON dbo.TimeKeeping_Plant_Activity_Codes.PAC_ACTIVITY_ID = dbo.TimeKeeping_Activity_Code_Master.AC_CVT_ID
;

GO
/****** Object:  View [dbo].[V_TimeKeeping_FRIDAY_EMP_ACT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[V_TimeKeeping_FRIDAY_EMP_ACT]
AS
SELECT        dbo.V_TimeKeeping_FRIDAY_EMP.FRIDAY, dbo.V_TimeKeeping_FRIDAY_EMP.EM_CVT_ID, dbo.V_TimeKeeping_FRIDAY_EMP.EM_PLANT_ID, dbo.V_PLANT_ACTIVITY_CODES.AC_CVT_ID
FROM            dbo.V_TimeKeeping_FRIDAY_EMP INNER JOIN
                         dbo.V_PLANT_ACTIVITY_CODES ON dbo.V_TimeKeeping_FRIDAY_EMP.EM_PLANT_ID = dbo.V_PLANT_ACTIVITY_CODES.PM_CVT_ID
WHERE        (dbo.V_PLANT_ACTIVITY_CODES.PAC_TIMEKEEPING_STATUS= 'A')
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_EMP_ACT_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_EMP_ACT_STS_HOURS]
AS
SELECT        EH_DATE_WORKED, EH_EMPLOYEE_ID, EH_ACTIVITY_CODE, EH_RECORDING_STATUS, SUM(EH_HOURS_WORKED) AS Total_Hours, EH_COMMENT
FROM            dbo.TimeKeeping_Employee_Hours
GROUP BY EH_DATE_WORKED, EH_EMPLOYEE_ID, EH_ACTIVITY_CODE, EH_RECORDING_STATUS, EH_COMMENT
GO
/****** Object:  View [dbo].[V_TimeKeeping_FRIDAY_EMP_ACT_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_FRIDAY_EMP_ACT_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_FRIDAY_EMP_ACT.FRIDAY, dbo.V_TimeKeeping_FRIDAY_EMP_ACT.EM_CVT_ID, dbo.V_TimeKeeping_FRIDAY_EMP_ACT.EM_PLANT_ID, dbo.V_TimeKeeping_FRIDAY_EMP_ACT.AC_CVT_ID, 
                         COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Status, COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.Total_Hours, 0) AS Hours, 
                         dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_COMMENT
FROM            dbo.V_TimeKeeping_FRIDAY_EMP_ACT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS ON dbo.V_TimeKeeping_FRIDAY_EMP_ACT.FRIDAY = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_FRIDAY_EMP_ACT.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_EMPLOYEE_ID AND 
                         dbo.V_TimeKeeping_FRIDAY_EMP_ACT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_EMP_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_EMP_STS_HOURS]
AS
SELECT        EH_DATE_WORKED, EH_EMPLOYEE_ID, EH_RECORDING_STATUS, SUM(EH_HOURS_WORKED) AS Total_Hours
FROM            dbo.TimeKeeping_Employee_Hours
WHERE        (EH_ACTIVITY_CODE <> 8013)
GROUP BY EH_DATE_WORKED, EH_EMPLOYEE_ID, EH_RECORDING_STATUS
GO
/****** Object:  View [dbo].[V_TimeKeeping_FRIDAY_EMP_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_FRIDAY_EMP_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_FRIDAY_EMP.FRIDAY, dbo.V_TimeKeeping_FRIDAY_EMP.EM_CVT_ID, dbo.V_TimeKeeping_FRIDAY_EMP.EM_PLANT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Status, 
                         COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_FRIDAY_EMP LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_STS_HOURS ON dbo.V_TimeKeeping_FRIDAY_EMP.FRIDAY = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_FRIDAY_EMP.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_EMPLOYEE_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_ACT_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[V_TimeKeeping_DATE_ACT_STS_HOURS]
AS
SELECT        
	dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED  
	,dbo.TimeKeeping_Employee_Master.EM_PLANT_ID 
	,dbo.TimeKeeping_Employee_Hours.EH_ACTIVITY_CODE 
	, dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS 
	, SUM(dbo.TimeKeeping_Employee_Hours.EH_HOURS_WORKED) AS Total_Hours 

FROM            
	dbo.TimeKeeping_Employee_Hours 
	INNER JOIN dbo.TimeKeeping_Employee_Master 
		ON dbo.TimeKeeping_Employee_Hours.EH_EMPLOYEE_ID = dbo.TimeKeeping_Employee_Master.EM_CVT_ID
GROUP BY 
	dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED
	, dbo.TimeKeeping_Employee_Hours.EH_ACTIVITY_CODE
	, dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS
	, dbo.TimeKeeping_Employee_Master.EM_PLANT_ID

GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_WORKED_STATUS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--drop view V_TimeKeeping_DATE_WORKED_STATUS;

CREATE view [dbo].[V_TimeKeeping_DATE_WORKED_STATUS] AS 
SELECT 
	DISTINCT dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EH_DATE_WORKED AS DW_DATE_WORKED
	,dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EM_PLANT_ID AS DW_PLANT_ID
	,coalesce(dbo.TimeKeeping_Date_Status.DS_INTERFACE_STATUS,'A') AS DW_STATUS 
	,dbo.TimeKeeping_Plant_Master.PM_NAME as DW_PLANT_NAME
	,dbo.TimeKeeping_Plant_Master.PM_TOKEN as DW_PLANT_TOKEN
	,coalesce(ahrs.total_hours_a,0) as DW_TOTAL_HOURS_ACTIVE
	,coalesce(shrs.total_hours_s,0) as DW_TOTAL_HOURS_SUBMITTED
FROM 
	dbo.V_TimeKeeping_DATE_ACT_STS_HOURS
	LEFT OUTER JOIN dbo.TimeKeeping_Date_Status
		ON EH_DATE_WORKED = DS_DATE
			and EM_PLANT_ID = DS_PLANT_ID
	inner join dbo.TimeKeeping_Plant_Master
		on dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EM_PLANT_ID = dbo.TimeKeeping_Plant_Master.PM_CVT_ID
	left join 
		(select sum(total_hours) as total_hours_a, EH_DATE_WORKED, EM_PLANT_ID from V_TimeKeeping_DATE_ACT_STS_HOURS hrsa
			where hrsa.EH_RECORDING_STATUS = 'A' 
			group by EH_DATE_WORKED, EM_PLANT_ID ) ahrs
		on 
			dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EH_DATE_WORKED = ahrs.EH_DATE_WORKED 
			and dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EM_PLANT_ID = ahrs.EM_PLANT_ID 
	left join 
		(select sum(total_hours) as total_hours_s, EH_DATE_WORKED, EM_PLANT_ID from V_TimeKeeping_DATE_ACT_STS_HOURS hrss
			where hrss.EH_RECORDING_STATUS = 'S' 
			group by EH_DATE_WORKED, EM_PLANT_ID ) shrs
		on 
			dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EH_DATE_WORKED = shrs.EH_DATE_WORKED 
			and dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EM_PLANT_ID = shrs.EM_PLANT_ID 


GO
/****** Object:  View [dbo].[V_TimeKeeping_YESTERDAY]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_YESTERDAY]
AS
SELECT        CONVERT(VARCHAR(10), DATEADD(Day, - 1, GETDATE()), 120) AS YESTERDAY
GO
/****** Object:  View [dbo].[V_TimeKeeping_YESTERDAY_EMP]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_YESTERDAY_EMP]
AS
SELECT        dbo.V_TimeKeeping_YESTERDAY.YESTERDAY, dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Employee_Master.EM_PLANT_ID
FROM            dbo.V_TimeKeeping_YESTERDAY CROSS JOIN
                         dbo.TimeKeeping_Employee_Master
GO
/****** Object:  View [dbo].[V_TimeKeeping_YESTERDAY_EMP_ACT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_YESTERDAY_EMP_ACT]
AS
SELECT        dbo.V_TimeKeeping_YESTERDAY_EMP.YESTERDAY, dbo.V_TimeKeeping_YESTERDAY_EMP.EM_CVT_ID, dbo.TimeKeeping_Plant_Activity_Codes.PAC_ACTIVITY_ID AS AC_CVT_ID
FROM            dbo.V_TimeKeeping_YESTERDAY_EMP INNER JOIN
                         dbo.TimeKeeping_Plant_Activity_Codes ON dbo.V_TimeKeeping_YESTERDAY_EMP.EM_PLANT_ID = dbo.TimeKeeping_Plant_Activity_Codes.PAC_PLANT_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_YESTERDAY_EMP_ACT_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_YESTERDAY_EMP_ACT_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_YESTERDAY_EMP_ACT.YESTERDAY, dbo.V_TimeKeeping_YESTERDAY_EMP_ACT.EM_CVT_ID, dbo.V_TimeKeeping_YESTERDAY_EMP_ACT.AC_CVT_ID, 
                         COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Status, COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.Total_Hours, 0) AS Hours, 
                         dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_COMMENT
FROM            dbo.V_TimeKeeping_YESTERDAY_EMP_ACT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS ON dbo.V_TimeKeeping_YESTERDAY_EMP_ACT.YESTERDAY = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_YESTERDAY_EMP_ACT.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_EMPLOYEE_ID AND 
                         dbo.V_TimeKeeping_YESTERDAY_EMP_ACT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_YESTERDAY_EMP_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_YESTERDAY_EMP_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_YESTERDAY_EMP.YESTERDAY, dbo.V_TimeKeeping_YESTERDAY_EMP.EM_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Status, 
                         COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_YESTERDAY_EMP LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_STS_HOURS ON dbo.V_TimeKeeping_YESTERDAY_EMP.YESTERDAY = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_YESTERDAY_EMP.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_EMPLOYEE_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY]
AS
SELECT        CONVERT(VARCHAR(10), GETDATE(), 120) AS Today
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_EMP]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_EMP]
AS
SELECT        dbo.V_TimeKeeping_TODAY.Today, dbo.TimeKeeping_Employee_Master.EM_CVT_ID
FROM            dbo.TimeKeeping_Employee_Master CROSS JOIN
                         dbo.V_TimeKeeping_TODAY
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_EMP_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_EMP_HOURS]
AS
SELECT        EH_DATE_WORKED, EH_EMPLOYEE_ID, SUM(EH_HOURS_WORKED) AS Total_Hours
FROM            dbo.TimeKeeping_Employee_Hours
GROUP BY EH_EMPLOYEE_ID, EH_DATE_WORKED
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_EMP_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_EMP_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_EMP.Today, dbo.V_TimeKeeping_TODAY_EMP.EM_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_TODAY_EMP LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_HOURS ON dbo.V_TimeKeeping_TODAY_EMP.Today = dbo.V_TimeKeeping_DATE_EMP_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_TODAY_EMP.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_HOURS.EH_EMPLOYEE_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_EMP_ACT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_EMP_ACT]
AS
SELECT        dbo.V_TimeKeeping_TODAY_EMP.Today, dbo.V_TimeKeeping_TODAY_EMP.EM_CVT_ID, dbo.TimeKeeping_Activity_Code_Master.AC_CVT_ID, TimeKeeping_Activity_Code_Master.AC_INTERFACE_TOKEN
FROM            dbo.V_TimeKeeping_TODAY_EMP CROSS JOIN
                         dbo.TimeKeeping_Activity_Code_Master
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_EMP_ACT_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_EMP_ACT_HOURS]
AS
SELECT        EH_DATE_WORKED, EH_EMPLOYEE_ID, EH_ACTIVITY_CODE, SUM(EH_HOURS_WORKED) AS Total_Hours
FROM            dbo.TimeKeeping_Employee_Hours
GROUP BY EH_EMPLOYEE_ID, EH_DATE_WORKED, EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_EMP_ACT_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_EMP_ACT_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_EMP_ACT.Today, dbo.V_TimeKeeping_TODAY_EMP_ACT.EM_CVT_ID, dbo.V_TimeKeeping_TODAY_EMP_ACT.AC_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_TODAY_EMP_ACT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_ACT_HOURS ON dbo.V_TimeKeeping_TODAY_EMP_ACT.Today = dbo.V_TimeKeeping_DATE_EMP_ACT_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_TODAY_EMP_ACT.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_HOURS.EH_EMPLOYEE_ID AND dbo.V_TimeKeeping_TODAY_EMP_ACT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_HOURS.EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_ACT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_ACT]
AS
SELECT        dbo.V_TimeKeeping_TODAY.Today, dbo.TimeKeeping_Activity_Code_Master.AC_CVT_ID
FROM            dbo.V_TimeKeeping_TODAY CROSS JOIN
                         dbo.TimeKeeping_Activity_Code_Master
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_ACT_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_ACT_HOURS]
AS
SELECT        EH_DATE_WORKED, EH_ACTIVITY_CODE, SUM(EH_HOURS_WORKED) AS Total_Hours
FROM            dbo.TimeKeeping_Employee_Hours
GROUP BY EH_DATE_WORKED, EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_ACT_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_ACT_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_ACT.Today, dbo.V_TimeKeeping_TODAY_ACT.AC_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_ACT_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_TODAY_ACT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_ACT_HOURS ON dbo.V_TimeKeeping_TODAY_ACT.Today = dbo.V_TimeKeeping_DATE_ACT_HOURS.EH_DATE_WORKED AND dbo.V_TimeKeeping_TODAY_ACT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_ACT_HOURS.EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_ACT_EMP]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_ACT_EMP]
AS
SELECT        dbo.V_TimeKeeping_TODAY_ACT.Today, dbo.V_TimeKeeping_TODAY_ACT.AC_CVT_ID, dbo.TimeKeeping_Employee_Master.EM_CVT_ID
FROM            dbo.V_TimeKeeping_TODAY_ACT CROSS JOIN
                         dbo.TimeKeeping_Employee_Master
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_ACT_EMP_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_ACT_EMP_HOURS]
AS
SELECT        EH_DATE_WORKED, EH_ACTIVITY_CODE, EH_EMPLOYEE_ID, SUM(EH_HOURS_WORKED) AS Total_Hours
FROM            dbo.TimeKeeping_Employee_Hours
GROUP BY EH_DATE_WORKED, EH_ACTIVITY_CODE, EH_EMPLOYEE_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_ACT_EMP_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_ACT_EMP_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_ACT_EMP.Today, dbo.V_TimeKeeping_TODAY_ACT_EMP.AC_CVT_ID, dbo.V_TimeKeeping_TODAY_ACT_EMP.EM_CVT_ID, SUM(COALESCE (dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.Total_Hours, 0)) AS Hours
FROM            dbo.V_TimeKeeping_TODAY_ACT_EMP LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS ON dbo.V_TimeKeeping_TODAY_ACT_EMP.Today = dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_TODAY_ACT_EMP.AC_CVT_ID = dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.EH_ACTIVITY_CODE AND dbo.V_TimeKeeping_TODAY_ACT_EMP.EM_CVT_ID = dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.EH_EMPLOYEE_ID
GROUP BY dbo.V_TimeKeeping_TODAY_ACT_EMP.Today, dbo.V_TimeKeeping_TODAY_ACT_EMP.AC_CVT_ID, dbo.V_TimeKeeping_TODAY_ACT_EMP.EM_CVT_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_EMP_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_EMP_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_EMP.Today, dbo.V_TimeKeeping_TODAY_EMP.EM_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Status, 
                         COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_TODAY_EMP LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_STS_HOURS ON dbo.V_TimeKeeping_TODAY_EMP.Today = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_TODAY_EMP.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_EMPLOYEE_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATETABLE_EMP_ACT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATETABLE_EMP_ACT]
AS
SELECT        TOP (100) PERCENT dbo.TimeKeeping_Date_Table.DT_DATE, dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Activity_Code_Master.AC_CVT_ID
FROM            dbo.TimeKeeping_Date_Table CROSS JOIN
                         dbo.TimeKeeping_Employee_Master CROSS JOIN
                         dbo.TimeKeeping_Activity_Code_Master
WHERE        (dbo.TimeKeeping_Activity_Code_Master.AC_TIMEKEEPING_STATUS = 'A')
ORDER BY dbo.TimeKeeping_Date_Table.DT_DATE, dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Activity_Code_Master.AC_DISPLAY_SEQ
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATETABLE_EMP_ACT_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATETABLE_EMP_ACT_STS_HOURS]
AS
SELECT        TOP (100) PERCENT dbo.V_TimeKeeping_DATETABLE_EMP_ACT.DT_DATE, dbo.V_TimeKeeping_DATETABLE_EMP_ACT.EM_CVT_ID, dbo.V_TimeKeeping_DATETABLE_EMP_ACT.AC_CVT_ID, 
                         COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Timekeeping_Status, COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.Total_Hours, 0) AS Hours, 
                         dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_COMMENT
FROM            dbo.V_TimeKeeping_DATETABLE_EMP_ACT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS ON dbo.V_TimeKeeping_DATETABLE_EMP_ACT.DT_DATE = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_DATETABLE_EMP_ACT.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_EMPLOYEE_ID AND 
                         dbo.V_TimeKeeping_DATETABLE_EMP_ACT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_ACTIVITY_CODE
ORDER BY dbo.V_TimeKeeping_DATETABLE_EMP_ACT.DT_DATE, dbo.V_TimeKeeping_DATETABLE_EMP_ACT.EM_CVT_ID, dbo.V_TimeKeeping_DATETABLE_EMP_ACT.AC_CVT_ID, Timekeeping_Status
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_ACT_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_ACT_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_ACT.Today, dbo.V_TimeKeeping_TODAY_ACT.AC_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Status, 
                         COALESCE (dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_TODAY_ACT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_ACT_STS_HOURS ON dbo.V_TimeKeeping_TODAY_ACT.Today = dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_TODAY_ACT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_ACT_STS_HOURS.EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATETABLE_EMP]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATETABLE_EMP]
AS
SELECT        dbo.TimeKeeping_Date_Table.DT_DATE AS The_Date, dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Employee_Master.EM_PLANT_ID
FROM            dbo.TimeKeeping_Date_Table CROSS JOIN
                         dbo.TimeKeeping_Employee_Master
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATETABLE_EMP_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATETABLE_EMP_HOURS]
AS
SELECT        dbo.V_TimeKeeping_DATETABLE_EMP.The_Date, dbo.V_TimeKeeping_DATETABLE_EMP.EM_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_DATETABLE_EMP LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_HOURS ON dbo.V_TimeKeeping_DATETABLE_EMP.The_Date = dbo.V_TimeKeeping_DATE_EMP_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_DATETABLE_EMP.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_HOURS.EH_EMPLOYEE_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_EMP_ACT_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_EMP_ACT_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_EMP_ACT.Today, dbo.V_TimeKeeping_TODAY_EMP_ACT.EM_CVT_ID, dbo.V_TimeKeeping_TODAY_EMP_ACT.AC_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_RECORDING_STATUS, 'A') 
                         AS Status, COALESCE (dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.Total_Hours, 0) AS Hours, dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_COMMENT
FROM            dbo.V_TimeKeeping_TODAY_EMP_ACT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS ON dbo.V_TimeKeeping_TODAY_EMP_ACT.Today = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_TODAY_EMP_ACT.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_EMPLOYEE_ID AND 
                         dbo.V_TimeKeeping_TODAY_EMP_ACT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_ACT_STS_HOURS.EH_ACTIVITY_CODE
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATETABLE_EMP_STS_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATETABLE_EMP_STS_HOURS]
AS
SELECT        dbo.V_TimeKeeping_DATETABLE_EMP.The_Date, dbo.V_TimeKeeping_DATETABLE_EMP.EM_CVT_ID, COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_RECORDING_STATUS, 'A') AS Status, 
                         COALESCE (dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.Total_Hours, 0) AS Hours
FROM            dbo.V_TimeKeeping_DATETABLE_EMP LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_EMP_STS_HOURS ON dbo.V_TimeKeeping_DATETABLE_EMP.The_Date = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_DATETABLE_EMP.EM_CVT_ID = dbo.V_TimeKeeping_DATE_EMP_STS_HOURS.EH_EMPLOYEE_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_PLANT_ACTIVITY_CODES]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_PLANT_ACTIVITY_CODES]
AS
SELECT        dbo.TimeKeeping_Plant_Master.PM_CVT_ID, dbo.TimeKeeping_Plant_Master.PM_NAME, dbo.TimeKeeping_Activity_Code_Master.AC_CVT_ID AS VAC_CVT_ID, COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_NAME, ''), 
                         dbo.TimeKeeping_Activity_Code_Master.AC_NAME, dbo.TimeKeeping_Plant_Activity_Codes.PAC_NAME) AS VAC_NAME, COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT, ''), dbo.TimeKeeping_Activity_Code_Master.AC_TEXT, 
                         dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT) AS VAC_TEXT, COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_DESCRIPTON, ''), dbo.TimeKeeping_Activity_Code_Master.AC_DESCRIPTON, 
                         dbo.TimeKeeping_Plant_Activity_Codes.PAC_DESCRIPTON) AS VAC_DESCRIPTION, dbo.TimeKeeping_Plant_Activity_Codes.PAC_INTERFACE_STATUS, dbo.TimeKeeping_Plant_Activity_Codes.PAC_DISPLAY_SEQ, 
                         dbo.TimeKeeping_Plant_Activity_Codes.PAC_TIMEKEEPING_STATUS, dbo.TimeKeeping_Activity_Code_Master.AC_INTERFACE_TOKEN, dbo.TimeKeeping_Activity_Code_Master.AC_CGL_PROCESS, dbo.TimeKeeping_Plant_Activity_Codes.PAC_LANGUAGE_ID, 
                         dbo.TimeKeeping_Activity_Code_Master.AC_LANGUAGE_ID
 
                         , COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT, ''), dbo.TimeKeeping_Activity_Code_Master.AC_TEXT_ES, dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT) AS VAC_TEXT_ES
                         , COALESCE (NULLIF (dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT, ''), dbo.TimeKeeping_Activity_Code_Master.AC_TEXT_PR, dbo.TimeKeeping_Plant_Activity_Codes.PAC_TEXT) AS VAC_TEXT_PR
FROM            dbo.TimeKeeping_Plant_Master INNER JOIN
                         dbo.TimeKeeping_Plant_Activity_Codes ON dbo.TimeKeeping_Plant_Master.PM_CVT_ID = dbo.TimeKeeping_Plant_Activity_Codes.PAC_PLANT_ID INNER JOIN
                         dbo.TimeKeeping_Activity_Code_Master ON dbo.TimeKeeping_Plant_Activity_Codes.PAC_ACTIVITY_ID = dbo.TimeKeeping_Activity_Code_Master.AC_CVT_ID
;
GO
/****** Object:  View [dbo].[V_TimeKeeping_NEW_EMP_PLANT_ACTIVITY]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_NEW_EMP_PLANT_ACTIVITY]
AS
SELECT        dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Employee_Master.EM_LAST_NAME, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_CVT_ID, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_NAME, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_TEXT, dbo.TimeKeeping_Employee_Master.EM_PLANT_ID, dbo.TimeKeeping_Employee_Master.EM_FIRST_NAME, dbo.TimeKeeping_Employee_Master.EM_STATUS_CODE, 
                         dbo.TimeKeeping_Employee_Master.EM_KEEPS_TIME, dbo.TimeKeeping_Employee_Master.EM_USER_PROFILE, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_INTERFACE_STATUS, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_TIMEKEEPING_STATUS, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.AC_INTERFACE_TOKEN, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.AC_CGL_PROCESS
FROM            dbo.TimeKeeping_Employee_Master LEFT OUTER JOIN
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES ON dbo.TimeKeeping_Employee_Master.EM_PLANT_ID = dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PM_CVT_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_ACT_STS_ME_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_ACT_STS_ME_HOURS]
AS
SELECT        dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PM_CVT_ID, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PM_NAME, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_DISPLAY_SEQ AS AC_DISPLAY_SEQ, dbo.TimeKeeping_Employee_Hours.EH_ACTIVITY_CODE, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_TEXT AS AC_TEXT, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_INTERFACE_STATUS AS AC_INTERFACE_STATUS, dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS, dbo.TimeKeeping_Employee_Hours.EH_INTERFACE_STATUS, 
                         SUM(dbo.TimeKeeping_Employee_Hours.EH_HOURS_WORKED) AS Total_Hours
FROM            dbo.TimeKeeping_Employee_Hours INNER JOIN
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES ON dbo.TimeKeeping_Employee_Hours.EH_ACTIVITY_CODE = dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_CVT_ID INNER JOIN
                         dbo.TimeKeeping_Employee_Master ON dbo.TimeKeeping_Employee_Hours.EH_EMPLOYEE_ID = dbo.TimeKeeping_Employee_Master.EM_CVT_ID AND 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PM_CVT_ID = dbo.TimeKeeping_Employee_Master.EM_PLANT_ID
GROUP BY dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED, dbo.TimeKeeping_Employee_Hours.EH_ACTIVITY_CODE, dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS, dbo.TimeKeeping_Employee_Hours.EH_INTERFACE_STATUS, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_DISPLAY_SEQ, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_TEXT, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_INTERFACE_STATUS, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PM_CVT_ID, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PM_NAME
GO
/****** Object:  View [dbo].[V_TimeKeeping_EMP_ACT_CODES]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_EMP_ACT_CODES]
AS
SELECT        TOP (100) PERCENT dbo.TimeKeeping_Employee_Master.EM_LAST_NAME, dbo.TimeKeeping_Employee_Master.EM_FIRST_NAME, dbo.TimeKeeping_Employee_Master.EM_CVT_ID, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_CVT_ID AS AC_CVT_ID, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.VAC_NAME AS AC_NAME, dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_DISPLAY_SEQ AS AC_DISPLAY_SEQ, 
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PAC_INTERFACE_STATUS AS AC_INTERFACE_STATUS
FROM            dbo.TimeKeeping_Employee_Master INNER JOIN
                         dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES ON dbo.TimeKeeping_Employee_Master.EM_PLANT_ID = dbo.V_TimeKeeping_PLANT_ACTIVITY_CODES.PM_CVT_ID
WHERE        (dbo.TimeKeeping_Employee_Master.EM_STATUS_CODE = 'A')
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_ACT_PLANT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_ACT_PLANT]
AS
SELECT        dbo.V_TimeKeeping_TODAY.Today, dbo.V_PLANT_ACTIVITY_CODES.AC_CVT_ID, dbo.V_PLANT_ACTIVITY_CODES.PM_CVT_ID
FROM            dbo.V_PLANT_ACTIVITY_CODES CROSS JOIN
                         dbo.V_TimeKeeping_TODAY
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_ACT_EMP_PLANT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_ACT_EMP_PLANT]
AS
SELECT        dbo.V_TimeKeeping_TODAY_ACT_PLANT.Today, dbo.V_TimeKeeping_TODAY_ACT_PLANT.AC_CVT_ID, dbo.V_TimeKeeping_TODAY_ACT_PLANT.PM_CVT_ID, dbo.TimeKeeping_Employee_Master.EM_CVT_ID
FROM            dbo.TimeKeeping_Employee_Master INNER JOIN
                         dbo.V_TimeKeeping_TODAY_ACT_PLANT ON dbo.TimeKeeping_Employee_Master.EM_PLANT_ID = dbo.V_TimeKeeping_TODAY_ACT_PLANT.PM_CVT_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_EMP_ACT_PLANT_HOURS]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_EMP_ACT_PLANT_HOURS]
AS
SELECT        dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.Today, dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.AC_CVT_ID, dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.EM_CVT_ID, SUM(COALESCE (dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.Total_Hours, 
                         0)) AS Hours
FROM            dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT LEFT OUTER JOIN
                         dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS ON dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.Today = dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.EH_DATE_WORKED AND 
                         dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.AC_CVT_ID = dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.EH_ACTIVITY_CODE AND 
                         dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.EM_CVT_ID = dbo.V_TimeKeeping_DATE_ACT_EMP_HOURS.EH_EMPLOYEE_ID
GROUP BY dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.Today, dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.AC_CVT_ID, dbo.V_TimeKeeping_TODAY_ACT_EMP_PLANT.EM_CVT_ID
GO
/****** Object:  View [dbo].[V_TimeKeeping_ACTIVITY_HOURS_BASE]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_ACTIVITY_HOURS_BASE] 
AS SELECT PAC1.[PAC_PLANT_ID]
      ,PAC1.[PAC_ACTIVITY_ID]
      --,PAC1.[PAC_CGL_ID]
      --,PAC1.[PAC_NAME]
      --,PAC1.[PAC_TEXT]
      ,CASE WHEN A1.AC_DESCRIPTON IS NULL THEN PAC1.[PAC_DESCRIPTON] 
	  ELSE A1.AC_DESCRIPTON END AS DESCRIPTION
      ,PAC1.[PAC_INTERFACE_TOKEN]
      ,PAC1.[PAC_INTERFACE_STATUS]
      ,PAC1.[PAC_PARENT_AC]
      ,PAC1.[PAC_DISPLAY_SEQ]
      ,PAC1.[PAC_CGL_PROCESS]
      ,PAC1.[PAC_TIMEKEEPING_STATUS]
	  ,A1.[AC_CGL_ID]
      ,A1.[AC_NAME]
      ,A1.[AC_TEXT]
      ,A1.[AC_DESCRIPTON]
      ,A1.[AC_INTERFACE_TOKEN]
      ,A1.[AC_INTERFACE_STATUS]
      ,A1.[AC_PARENT_AC]
      ,A1.[AC_DISPLAY_SEQ]
      ,A1.[AC_CGL_PROCESS]
      ,A1.[AC_TIMEKEEPING_STATUS]
      ,P1.[PM_NAME]
      ,P1.[PM_CITY]
      ,P1.[PM_STATE_PROVINCE]
      ,P1.[PM_COUNTRY_CODE]
      ,P1.[PM_STATUS]
      ,P1.[PM_TOKEN]
      ,P1.[PM_IS_GRAIN]
      ,P1.[PM_IS_MILL]
	  ,EM1.[EM_FIRST_NAME]
      ,EM1.[EM_LAST_NAME]
      ,EM1.[EM_TITLE]
      ,EM1.[EM_STATUS_CODE]
	  ,EM1.[EM_USER_PROFILE]
	  ,EH1.[EH_EMPLOYEE_ID]
      ,EH1.[EH_DATE_WORKED]
      ,EH1.[EH_ACTIVITY_CODE]
      ,EH1.[EH_HOURS_WORKED]
      ,EH1.[EH_DATE_RECORDED]
      ,EH1.[EH_TIME_RECORDED]
      ,EH1.[EH_RECORDING_STATUS]
  FROM [dbo].[TimeKeeping_Plant_Activity_Codes] PAC1
  LEFT OUTER JOIN TimeKeeping_Activity_Code_Master A1 
  ON PAC1.PAC_ACTIVITY_ID = A1.AC_CVT_ID
  --JOIN TO PLANT MASTER
  LEFT OUTER JOIN TimeKeeping_Plant_Master AS P1
  ON PAC1.PAC_PLANT_ID = P1.PM_CVT_ID
  -- JOIN TO TK_EMPLOYEE MASTER
  LEFT OUTER JOIN [TimeKeeping_Employee_Master] AS EM1
  ON EM1.EM_PLANT_ID = PAC1.PAC_PLANT_ID
  -- JOIN TO TK_EMPLOYEE HOURS
  LEFT OUTER JOIN [TimeKeeping_Employee_Hours] EH1
  ON EH1.EH_EMPLOYEE_ID = EM1.EM_CVT_ID

GO
/****** Object:  View [dbo].[V_TimeKeeping_ACTIVITY_HOURS_BASEV2]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_ACTIVITY_HOURS_BASEV2] 
AS SELECT PAC1.[PAC_PLANT_ID]
      ,PAC1.[PAC_ACTIVITY_ID]
      --,PAC1.[PAC_CGL_ID]
      --,PAC1.[PAC_NAME]
      --,PAC1.[PAC_TEXT]
      ,CASE WHEN A1.AC_DESCRIPTON IS NULL THEN PAC1.[PAC_DESCRIPTON] 
	  ELSE A1.AC_DESCRIPTON END AS DESCRIPTION
      ,PAC1.[PAC_INTERFACE_TOKEN]
      ,PAC1.[PAC_INTERFACE_STATUS]
      ,PAC1.[PAC_PARENT_AC]
      ,PAC1.[PAC_DISPLAY_SEQ]
      ,PAC1.[PAC_CGL_PROCESS]
      ,PAC1.[PAC_TIMEKEEPING_STATUS]
	  ,A1.[AC_CGL_ID]
      ,A1.[AC_NAME]
      ,A1.[AC_TEXT]
      ,A1.[AC_DESCRIPTON]
      ,A1.[AC_INTERFACE_TOKEN]
      ,A1.[AC_INTERFACE_STATUS]
      ,A1.[AC_PARENT_AC]
      ,A1.[AC_DISPLAY_SEQ]
      ,A1.[AC_CGL_PROCESS]
      ,A1.[AC_TIMEKEEPING_STATUS]
      ,P1.[PM_NAME]
      ,P1.[PM_CITY]
      ,P1.[PM_STATE_PROVINCE]
      ,P1.[PM_COUNTRY_CODE]
      ,P1.[PM_STATUS]
      ,P1.[PM_TOKEN]
      ,P1.[PM_IS_GRAIN]
      ,P1.[PM_IS_MILL]
	  ,EM1.[EM_FIRST_NAME]
      ,EM1.[EM_LAST_NAME]
      ,EM1.[EM_TITLE]
      ,EM1.[EM_STATUS_CODE]
	  ,EM1.[EM_USER_PROFILE]
	  ,EH1.[EH_EMPLOYEE_ID]
     -- ,EH1.[EH_DATE_WORKED]
      ,EH1.[EH_ACTIVITY_CODE]
      ,EH1.[EH_HOURS_WORKED]
	  ,D1.DT_DATE as DATE_WORKED
      ,EH1.[EH_DATE_RECORDED]
      ,EH1.[EH_TIME_RECORDED]
      ,EH1.[EH_RECORDING_STATUS]
  FROM [dbo].[TimeKeeping_Plant_Activity_Codes] PAC1
  -- JOIN TO TimeKeeping_Date_Table
  LEFT OUTER JOIN TimeKeeping_Date_Table D1 ON 1=1
  -- JOIN TO ACTIVITY CODE MASTER
  LEFT OUTER JOIN TimeKeeping_Activity_Code_Master A1 
  ON PAC1.PAC_ACTIVITY_ID = A1.AC_CVT_ID
  --JOIN TO PLANT MASTER
  LEFT OUTER JOIN TimeKeeping_Plant_Master AS P1
  ON PAC1.PAC_PLANT_ID = P1.PM_CVT_ID
  -- JOIN TO TK_EMPLOYEE MASTER
  LEFT OUTER JOIN [TimeKeeping_Employee_Master] AS EM1
  ON EM1.EM_PLANT_ID = PAC1.PAC_PLANT_ID
  -- JOIN TO TK_EMPLOYEE HOURS
  LEFT OUTER JOIN [TimeKeeping_Employee_Hours] EH1
  ON EH1.EH_EMPLOYEE_ID = EM1.EM_CVT_ID
     AND EH1.EH_ACTIVITY_CODE = PAC1.PAC_ACTIVITY_ID
     AND D1.DT_DATE = EH1.EH_DATE_WORKED
GO
/****** Object:  View [dbo].[V_TimeKeeping_Daily_Act_Hours]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_Daily_Act_Hours]
AS
SELECT        EH_DATE_WORKED, EH_ACTIVITY_CODE, EH_RECORDING_STATUS, SUM(EH_HOURS_WORKED) AS [Total Hours]
FROM            dbo.TimeKeeping_Employee_Hours
GROUP BY EH_DATE_WORKED, EH_ACTIVITY_CODE, EH_RECORDING_STATUS
GO
/****** Object:  View [dbo].[V_TimeKeeping_Daily_Emp_Hours]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_Daily_Emp_Hours]
AS
SELECT        dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Employee_Master.EM_LAST_NAME + ', ' + dbo.TimeKeeping_Employee_Master.EM_FIRST_NAME AS FullName, dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED, 
                         dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS, SUM(dbo.TimeKeeping_Employee_Hours.EH_HOURS_WORKED) AS [Total Daily Hours]
FROM            dbo.TimeKeeping_Employee_Hours RIGHT OUTER JOIN
                         dbo.TimeKeeping_Employee_Master ON dbo.TimeKeeping_Employee_Hours.EH_EMPLOYEE_ID = dbo.TimeKeeping_Employee_Master.EM_CVT_ID
GROUP BY dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED, dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS, 
                         dbo.TimeKeeping_Employee_Master.EM_LAST_NAME + ', ' + dbo.TimeKeeping_Employee_Master.EM_FIRST_NAME
GO
/****** Object:  View [dbo].[V_TimeKeeping_Daily_Emp_HoursbyPlant]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_Daily_Emp_HoursbyPlant]
AS
SELECT        dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Employee_Master.EM_LAST_NAME + ', ' + dbo.TimeKeeping_Employee_Master.EM_FIRST_NAME AS FullName, dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED, 
                         dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS, SUM(dbo.TimeKeeping_Employee_Hours.EH_HOURS_WORKED) AS [Total Daily Hours],
                         dbo.TimeKeeping_Employee_Master.EM_PLANT_ID, dbo.TimeKeeping_Plant_Master.PM_Name, COALESCE(dbo.TimeKeeping_Date_Status.DS_INTERFACE_STATUS, 'W') as Interface_Status
FROM            dbo.TimeKeeping_Employee_Hours 
						inner JOIN
 dbo.TimeKeeping_Employee_Master ON dbo.TimeKeeping_Employee_Hours.EH_EMPLOYEE_ID = dbo.TimeKeeping_Employee_Master.EM_CVT_ID
						inner join dbo.TimeKeeping_Plant_Master ON dbo.TimeKeeping_Plant_Master.PM_CVT_ID = dbo.TimeKeeping_Employee_Master.EM_PLANT_ID
						left outer JOIN dbo.TimeKeeping_Date_Status on dbo.TimeKeeping_Date_Status.DS_PLANT_ID = dbo.TimeKeeping_Employee_Master.EM_PLANT_ID 
							and dbo.TimeKeeping_Employee_Master.EM_CVT_ID = dbo.TimeKeeping_Date_Status.DS_EMPLOYEE_ID
							and dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED = dbo.TimeKeeping_Date_Status.DS_DATE
GROUP BY dbo.TimeKeeping_Employee_Master.EM_CVT_ID, dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED, dbo.TimeKeeping_Employee_Hours.EH_RECORDING_STATUS, 
                         dbo.TimeKeeping_Employee_Master.EM_LAST_NAME + ', ' + dbo.TimeKeeping_Employee_Master.EM_FIRST_NAME
,dbo.TimeKeeping_Employee_Master.EM_PLANT_ID,
                         dbo.TimeKeeping_Plant_Master.PM_Name, dbo.TimeKeeping_Date_Status.DS_INTERFACE_STATUS
;
GO
/****** Object:  View [dbo].[V_TimeKeeping_DATE_PLANT]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_DATE_PLANT]
AS
SELECT        TOP (100) PERCENT dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED, dbo.TimeKeeping_Plant_Master.PM_NAME, dbo.TimeKeeping_Plant_Master.PM_CVT_ID
, 
				coalesce(TimeKeeping_Date_Status.DS_INTERFACE_STATUS,'A') AS DS_INTERFACE_STATUS 

FROM            dbo.TimeKeeping_Employee_Hours 
						 INNER JOIN
 dbo.TimeKeeping_Employee_Master ON dbo.TimeKeeping_Employee_Hours.EH_EMPLOYEE_ID = dbo.TimeKeeping_Employee_Master.EM_CVT_ID 
                         INNER JOIN
 dbo.TimeKeeping_Plant_Master ON dbo.TimeKeeping_Employee_Master.EM_PLANT_ID = dbo.TimeKeeping_Plant_Master.PM_CVT_ID
                         left outer join dbo.TimeKeeping_Date_Status on dbo.TimeKeeping_Plant_Master.PM_CVT_ID = dbo.TimeKeeping_Date_Status.DS_PLANT_ID and dbo.TimeKeeping_Date_Status.DS_DATE = dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED

GROUP BY dbo.TimeKeeping_Plant_Master.PM_CVT_ID, dbo.TimeKeeping_Plant_Master.PM_NAME, dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED
, TimeKeeping_Date_Status.DS_INTERFACE_STATUS
ORDER BY dbo.TimeKeeping_Employee_Hours.EH_DATE_WORKED DESC, dbo.TimeKeeping_Plant_Master.PM_NAME
GO
/****** Object:  View [dbo].[V_TimeKeeping_TODAY_TABLE]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_TimeKeeping_TODAY_TABLE]
AS
SELECT        DT_DATE AS Today
FROM            dbo.TimeKeeping_Date_Table
GO
ALTER TABLE [dbo].[TimeKeeping_Activity_Code_Master] ADD  DEFAULT ('A') FOR [AC_INTERFACE_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Activity_Code_Master] ADD  CONSTRAINT [DF_TimeKeeping_Activity_Code_Master_AC_TIMEKEPING_STATUS]  DEFAULT ('A') FOR [AC_TIMEKEEPING_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Activity_Code_Master] ADD  CONSTRAINT [DF_TimeKeeping_Activity_Code_Master_AC_UPDATED_DATE]  DEFAULT (getdate()) FOR [AC_UPDATED_DATE]
GO
ALTER TABLE [dbo].[TimeKeeping_Activity_Code_Master] ADD  CONSTRAINT [DF_TimeKeeping_Activity_Code_Master_AC_INSERT_DATE]  DEFAULT (getdate()) FOR [AC_INSERT_DATE]
GO
ALTER TABLE [dbo].[TimeKeeping_Date_Status] ADD  CONSTRAINT [DF__TK_Date_S__DS_DA]  DEFAULT (CONVERT([date],getdate())) FOR [DS_DATE]
GO
ALTER TABLE [dbo].[TimeKeeping_Date_Status] ADD  CONSTRAINT [DF__TK_Date_S__DS_IN]  DEFAULT ('A') FOR [DS_INTERFACE_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_DateActivity_Status] ADD  CONSTRAINT [DF__TK_DateAc__AS_DA__2EDAF651]  DEFAULT (CONVERT([date],getdate())) FOR [AS_DATE]
GO
ALTER TABLE [dbo].[TimeKeeping_DateActivity_Status] ADD  CONSTRAINT [DF__TK_DateAc__AS_IN__2FCF1A8A]  DEFAULT ('A') FOR [AS_INTERFACE_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_DateActivity_Status] ADD  CONSTRAINT [DF_TimeKeeping_DateActivity_Status_AS_DATE_CREATED]  DEFAULT (getdate()) FOR [AS_DATE_CREATED]
GO
ALTER TABLE [dbo].[TimeKeeping_Department_Master] ADD  DEFAULT ('A') FOR [DM_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Hours] ADD  DEFAULT (CONVERT([date],getdate())) FOR [EH_DATE_RECORDED]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Hours] ADD  DEFAULT (CONVERT([time],getdate())) FOR [EH_TIME_RECORDED]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Hours] ADD  DEFAULT ('A') FOR [EH_RECORDING_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Hours] ADD  DEFAULT ('N') FOR [EH_INTERFACE_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Hours] ADD  DEFAULT ('0000') FOR [EH_INTERFACE_ERROR_CODE]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Master] ADD  DEFAULT ('A') FOR [EM_STATUS_CODE]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Master] ADD  DEFAULT ('Y') FOR [EM_KEEPS_TIME]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Master] ADD  DEFAULT ('N') FOR [EM_IS_SUP]
GO
ALTER TABLE [dbo].[TimeKeeping_Employee_Master] ADD  DEFAULT ('N') FOR [EM_IS_ADMIN]
GO
ALTER TABLE [dbo].[TimeKeeping_Plant_Activity_Codes] ADD  DEFAULT ('A') FOR [PAC_INTERFACE_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Plant_Activity_Codes] ADD  CONSTRAINT [DF_TimeKeeping_Plant_Activity_Codes_TIMEKEPING_STATUS]  DEFAULT ('A') FOR [PAC_TIMEKEEPING_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Plant_Activity_Codes] ADD  CONSTRAINT [DF_TimeKeeping_Plant_Activity_Codes_PAC_Updated_Date]  DEFAULT (getdate()) FOR [PAC_UPDATED_DATE]
GO
ALTER TABLE [dbo].[TimeKeeping_Plant_Master] ADD  DEFAULT ('A') FOR [PM_STATUS]
GO
ALTER TABLE [dbo].[TimeKeeping_Plant_Master] ADD  DEFAULT ('Y') FOR [PM_IS_GRAIN]
GO
ALTER TABLE [dbo].[TimeKeeping_Plant_Master] ADD  DEFAULT ('N') FOR [PM_IS_MILL]
GO
ALTER TABLE [dbo].[TimeKeeping_Plant_Master] ADD  CONSTRAINT [DF_TimeKeeping_Plant_Master_PM_Update_Date]  DEFAULT (getdate()) FOR [PM_Update_Date]
GO
ALTER TABLE [dbo].[TimeKeeping_Shift_Master] ADD  DEFAULT ('A') FOR [SM_STATUS]
GO
/****** Object:  StoredProcedure [dbo].[sp_TimeKeeping_ApproveTime]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Kris Catavolt, Inc >
-- Create date: <Jan 28, 2019>
-- Description:	<sp_TimeKeeping_ApproveTime>
-- =============================================
CREATE procedure [dbo].[sp_TimeKeeping_ApproveTime] @p_date date, @p_employee_id int, @p_plant_id int, @p_message varchar(2048) output
as

declare @w_interfaceStatus varchar(1)
declare @w_pk int
declare @w_message nvarchar(2048) = ''
declare @w_msgType varchar(20) = 'error'   
declare @w_active_count int
declare @w_approved_count int
declare @w_count int


-- Can this be approved?
select @w_active_count = count(*) from V_TimeKeeping_Daily_Emp_HoursbyPlant ad WHERE ad.EM_CVT_ID = @p_employee_id and [EM_PLANT_ID] = @p_plant_id and [EH_DATE_WORKED] = @p_date AND [Total Daily Hours] > 0 AND ad.EH_RECORDING_STATUS = 'A';

if @w_active_count > 0
	begin
		set @w_message = 'All recorded time activity must be marked as Submitted to be Approved.'
		set @p_message = '<Response>' 
					+ '<Message type=''' + @w_msgType + '''><Text>'
					+ @w_message
					+ '</Text></Message></Response>'
		return;
	end

select @w_count = count(*), @w_interfaceStatus = [DS_INTERFACE_STATUS] from TimeKeeping_Date_Status ad 
	WHERE  [DS_DATE] = @p_date and [DS_PLANT_ID] = @p_plant_id and [DS_EMPLOYEE_ID] = @p_employee_id group by DS_INTERFACE_STATUS;
IF(@w_count IS NULL OR @w_count = 0)
	begin
		--no record found insert record with approve
		INSERT INTO dbo.TimeKeeping_Date_Status (DS_DATE, DS_PLANT_ID, DS_EMPLOYEE_ID, DS_INTERFACE_STATUS)
		VALUES (@p_date, @p_plant_id, @p_employee_id, 'A');
		return;
	end


-- approved already?
if @w_interfaceStatus = 'A'
	begin
		set @w_message = 'Time sheet has been Approved for this date ' + convert(varchar(10), @p_date, 101)
		set @p_message = '<Response>' 
					+ '<Message type=''' + @w_msgType + '''><Text>'
					+ @w_message
					+ '</Text></Message></Response>'
		return;
	end



--update to approved


UPDATE [dbo].[TimeKeeping_Date_Status]  SET [DS_INTERFACE_STATUS] = 'A' WHERE  [DS_DATE] = @p_date and [DS_PLANT_ID] = @p_plant_id and [DS_EMPLOYEE_ID] = @p_employee_id;
GO
/****** Object:  StoredProcedure [dbo].[sp_TimeKeeping_Insert_Hours]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Tim Eliasen Catavolt, Inc >
-- Create date: <Aug 8, 2017>
-- Description:	<Insert hours from Catavolt Timekeeping app>
-- =============================================
CREATE PROCEDURE [dbo].[sp_TimeKeeping_Insert_Hours] 

	-- Add the parameters for the stored procedure here>

	@P_EMP_ID int,
	@P_ACT_CODE int,
	@P_DATE_WORKED date,
	@P_HOURS_WORKED [decimal] (4,2),
	@P_Comment varchar(50),
	@p_LanguageID nvarchar(20),
	@p_message varchar(2048) output					-- wmaia

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statement for procedure here
	-- T. Eliasen 8/8/17: query table to locate a record (Employee Hours)
	--                     If record found, update the hours
	--                     If not found, add the row to the table  
	
	-- wmaia begin
	declare @w_crLf char (2) =  char(0x0D) + char(0x0A)  --  carriage return + new line
  
	declare @w_message nvarchar(2048) = ' '
	declare @w_messageInt nvarchar(2048) = ' '
	declare @w_msgType varchar(20) = 'information' 
	declare @w_decimal_hours_worked decimal (3,2)	
	declare @w_labelID int =   45
	
	

	if	exists (select [EH_EMPLOYEE_ID]
					from [dbo].[TimeKeeping_Employee_Hours]
						where  [EH_EMPLOYEE_ID] = @P_EMP_ID 
							and [EH_DATE_WORKED] = @P_DATE_WORKED
							and [EH_RECORDING_STATUS] = 'S')
		begin
			-- get message based on language
			set @w_messageInt = ''
			set @w_labelID =   45
			select @w_messageInt = [LabelContent]
			from [dbo].[LanguageLabels]
			where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
			set	@w_message = @w_message + @w_messageInt
		end
	else
		begin
--			if @P_HOURS_WORKED <= 0 or @P_HOURS_WORKED is null 
--				set	@w_message =  'Hours worked should be greater than 0. '
			set @w_messageInt = ''
			set @w_labelID =   46
			
			if @P_ACT_CODE is null or @P_ACT_CODE = '  '
				select @w_messageInt = [LabelContent]
				from [dbo].[LanguageLabels]
				where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
				
			set	@w_message = @w_message + @w_messageInt
			--set @w_message = @w_message + 'You must enter an Activity Code. ' 
			set @w_decimal_hours_worked = @P_HOURS_WORKED - floor(@P_HOURS_WORKED)
			
			set @w_messageInt = ''
			set @w_labelID =   40
			if @w_decimal_hours_worked not in (0.25, 0.50, 0.75, 00)
				select @w_messageInt = [LabelContent]
				from [dbo].[LanguageLabels]
				where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
			set	@w_message = @w_message + @w_messageInt	
			--set	@w_message = @w_message + 'Hours worked should be with 0.25 increments. '
			
			set @w_messageInt = ''
			set @w_labelID =   41
			if @P_HOURS_WORKED > 24
				begin
					select @w_messageInt = [LabelContent]
					from [dbo].[LanguageLabels]
					where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
					set	@w_message = @w_message + @w_messageInt
					--set	@w_message = @w_message + 'Employee cannot work more than 24 hours per day. '
				end
			else
				begin
					declare @w_day_total_hours decimal (4,2)  
					set @w_day_total_hours = (select sum([EH_HOURS_WORKED]) 
							from [dbo].[TimeKeeping_Employee_Hours]
								where  [EH_EMPLOYEE_ID] = @P_EMP_ID and [EH_DATE_WORKED] = @P_DATE_WORKED
							group by [EH_EMPLOYEE_ID])
					declare @w_act_hours decimal (4,2) 
					select @w_act_hours = EH_HOURS_WORKED
						from [dbo].[TimeKeeping_Employee_Hours]
							where  [EH_EMPLOYEE_ID] = @P_EMP_ID 
								and [EH_DATE_WORKED] = @P_DATE_WORKED
								and [EH_ACTIVITY_CODE]	= @P_ACT_CODE
					if @w_act_hours is null
						set @w_act_hours = 0
					set @w_day_total_hours = @w_day_total_hours + @P_HOURS_WORKED
					set @w_act_hours = @w_act_hours  + @P_HOURS_WORKED
					set @w_messageInt = ''
					set @w_labelID =   41
					if (@w_day_total_hours) > 24
						select @w_messageInt = [LabelContent]
						from [dbo].[LanguageLabels]
						where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
					set	@w_message = @w_message + @w_messageInt
					--set	@w_message = @w_message + 'Employee cannot work more than 24 hours per day. '				
				end
		end
	if @w_message = ''
		begin
			-- wmaia end
			
			If Exists (Select EH_CVT_TRXN_ID  
							from TimeKeeping_Employee_Hours 
							where 
								EH_EMPLOYEE_ID		= @P_EMP_ID		AND
								EH_ACTIVITY_CODE	= @P_ACT_CODE	AND
								EH_DATE_WORKED		= @P_DATE_WORKED
							)
				begin
				   -- update the record from the table
				    
				   	if @w_act_hours < 0 
				   		begin
							set @w_labelID =   42
							select @w_message = [LabelContent]
							from [dbo].[LanguageLabels]
							where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
							--set	@w_message = @w_message + @w_messageInt
							--set	@w_message =  'Hours worked should be greater than 0. '
						end
					else
						begin
						   update dbo.TimeKeeping_Employee_Hours 
						   Set EH_HOURS_WORKED = @w_act_hours,
							EH_COMMENT = @P_Comment
						   where 
								EH_EMPLOYEE_ID		= @P_EMP_ID		AND
								EH_ACTIVITY_CODE	= @P_ACT_CODE	AND
								EH_DATE_WORKED		= @P_DATE_WORKED
							-- set	@w_message = 'Record updated sucessfully. '	 removed as requested by customer
						end
				end	
			Else
				begin
				   -- insert the row into the table
					if @P_HOURS_WORKED <= 0 
						begin
							set @w_labelID =   42
							select @w_message = [LabelContent]
							from [dbo].[LanguageLabels]
							where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
							--set	@w_message =  'Hours worked should be greater than 0. '
						end
						
					else
						begin
						/*
							declare @w_shift int
							select  @w_shift = [EM_CURRENT_SHIFT]
								from [dbo].[TimeKeeping_Employee_Master]
									where [EM_CVT_ID] = @P_EMP_ID
							if  @w_shift = 4001
								begin
									declare @w_endShifTime time 
									select @w_endShifTime  = [SM_END_TIME]
										from [dbo].[TimeKeeping_Shift_Master]
											where [SM_CVT_ID] = 4001
									if datepart(hour, current_timestamp) > 
										datepart(hour, @w_endShifTime)
											set @P_DATE_WORKED = DATEADD(dd,1,getdate())
								end	
						*/
						   INSERT INTO  dbo.TimeKeeping_Employee_Hours (
							   EH_ACTIVITY_CODE,
							   EH_DATE_WORKED,
							   EH_EMPLOYEE_ID,
							   EH_HOURS_WORKED,
							   EH_COMMENT
						   )
						   VALUES(
							   @P_ACT_CODE,
							   @P_DATE_WORKED,			-- (CONVERT([date],getdate())),
							   @P_EMP_ID,
							   @w_act_hours,
							   @P_Comment
						   )
							-- set	@w_message = 'Record updated sucessfully. '	 removed as requested by customer
						end
				end
			end
		end									-- wmaia
	if @w_message = ''	
		set @p_message = null
	else
		set @p_message = '<Response>' 
				+ '<Message type=''' + @w_msgType + '''><Text>'
				+ @w_message
				+ '</Text></Message></Response>'









GO
/****** Object:  StoredProcedure [dbo].[sp_TimeKeeping_resetSubmit]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Walter Maia Catavolt, Inc >
-- Create date: <Aug 18, 2017>
-- Description:	<Reset Submitted Daily Activity>
-- =============================================
CREATE procedure [dbo].[sp_TimeKeeping_resetSubmit] @p_date date, @p_employee_id int, @p_LanguageID nvarchar(20), @p_message varchar(2048) output
as

declare @w_message nvarchar(2048) = ''
declare @w_msgType varchar(20) = 'warning'
declare @w_labelID int =   39

if not exists (select DS_INTERFACE_STATUS 
			from  dbo.TimeKeeping_Date_Status 
			where DS_DATE = @p_date  and  DS_EMPLOYEE_ID = @p_employee_id and DS_INTERFACE_STATUS = 'A')
	update [dbo].[TimeKeeping_Employee_Hours]
		set [EH_RECORDING_STATUS] = 'A'
			where [EH_EMPLOYEE_ID] = @p_employee_id and [EH_DATE_WORKED] = @p_date	
else
	begin
		-- get message based on language
		select @w_message = [LabelContent]
				from [dbo].[LanguageLabels]
					where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
					
					
		--set @w_message = 'Reset was not done. Timesheet already processed.'						
		set @p_message = '<Response>' 
						+ '<Message type=''' + @w_msgType + '''><Text>'
						+ @w_message
						+ '</Text></Message></Response>'
	end




GO
/****** Object:  StoredProcedure [dbo].[sp_TimeKeeping_Submit]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Highlight and execute the following statement to drop the procedure
before executing the create statement.

DROP PROCEDURE dbo.sp_TimeKeeping_Submit;

*/



-- =============================================
-- Author:		<Walter Maia Catavolt, Inc >
-- Create date: <Aug 18, 2017>
-- Description:	<Submit Daily Activity>
-- =============================================
CREATE procedure [dbo].[sp_TimeKeeping_Submit] @p_date date, @p_employee_id int, @p_LanguageID nvarchar(20), @p_message varchar(2048) output
as

declare @w_day_total_hours decimal (4,2)
declare @w_message nvarchar(2048) = ' '
declare @w_labelID int =   43


if exists 	(SELECT  [EH_HOURS_WORKED] FROM [dbo].[TimeKeeping_Employee_Hours]
				where  [EH_EMPLOYEE_ID] = @p_employee_id
					and [EH_DATE_WORKED] = @p_date
					and [EH_RECORDING_STATUS] = 'S')
		begin
		
			select @w_message = [LabelContent]
			from [dbo].[LanguageLabels]
			where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
			
			raiserror (@w_message, 16,1)
			--raiserror ('Timesheet already submited. ', 16,1)
			RETURN (1) 
		end


SET @w_day_total_hours = (select sum([EH_HOURS_WORKED]) 
			from [dbo].[TimeKeeping_Employee_Hours]
				where  [EH_EMPLOYEE_ID] = @p_employee_id and [EH_DATE_WORKED] = @p_date
					and EH_ACTIVITY_CODE <> 8013
			group by [EH_EMPLOYEE_ID],[EH_DATE_WORKED])

declare @w_msgType varchar(20) = 'error'   
set @w_message = ' '
set @w_labelID =   44	
if 	@w_day_total_hours <= 0 or @w_day_total_hours is null
	begin
		select @w_message = [LabelContent]
		from [dbo].[LanguageLabels]
		where  [LabelID] = @w_labelID and [LanguageID] = @p_LanguageID
		--set @w_message = 'Timesheet does not have any hour. Please update and submit again.' 
		set @p_message = '<Response>' 
						+ '<Message type=''' + @w_msgType + '''><Text>'
						+ @w_message
						+ '</Text></Message></Response>'		
	end
else
	begin
		
				declare @w_OverHours decimal (4,2) 
				set @w_OverHours =(SELECT  [EH_HOURS_WORKED]
									FROM [dbo].[TimeKeeping_Employee_Hours]
										where  [EH_EMPLOYEE_ID] = @p_employee_id
										  and [EH_DATE_WORKED] = @p_date
										  and EH_ACTIVITY_CODE = 8013)
				if 	@w_OverHours is null
					begin
					if @w_day_total_hours > 8 
						begin
							set @w_OverHours =  @w_day_total_hours - 8
							insert into [dbo].[TimeKeeping_Employee_Hours](
									[EH_EMPLOYEE_ID], [EH_DATE_WORKED]
								   ,[EH_ACTIVITY_CODE], [EH_HOURS_WORKED]
									)
								values
								   (@p_employee_id, @p_date
								   , 8013, @w_OverHours)
						end
					end
				else	
					begin
						if @w_day_total_hours > 8
							set @w_OverHours =  @w_day_total_hours - 8
						else
							set @w_OverHours = 0
						update [dbo].[TimeKeeping_Employee_Hours]
							set EH_HOURS_WORKED = @w_OverHours
							where [EH_DATE_WORKED] = @p_date
								and [EH_EMPLOYEE_ID] = @p_employee_id
								and [EH_ACTIVITY_CODE] = 8013

					end
			
		update [dbo].[TimeKeeping_Employee_Hours]
		    set [EH_RECORDING_STATUS] = 'S'
			where [EH_DATE_WORKED] = @p_date
				and [EH_EMPLOYEE_ID] = @p_employee_id
				and [EH_RECORDING_STATUS] = 'A'
	--	set @w_message = 'Day activities submited.'
	--	set @w_msgType = 'information'
	end

GO
/****** Object:  StoredProcedure [dbo].[sp_TimeKeeping_validateMESubmit]    Script Date: 1/31/2019 10:12:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Walter Maia Catavolt, Inc >
-- Create date: <Aug 18, 2017>
-- Description:	<Submit Daily Activity>
-- =============================================
CREATE procedure [dbo].[sp_TimeKeeping_validateMESubmit] @p_date date, @p_message varchar(2048) output
as

declare @w_interfaceStatus varchar(1)
declare @w_pk int
declare @w_message nvarchar(2048) = ''
declare @w_msgType varchar(20) = 'error'   
declare @w_active_count int
declare @w_submitted_count int

select @w_interfaceStatus = [DS_INTERFACE_STATUS] from [dbo].[TimeKeeping_Date_Status] WHERE [DS_DATE] = @p_date;

-- Submitted already?
if @w_interfaceStatus = 'S'
	begin
		set @w_message = 'Time activity has been submitted to Model Elevator for this date ' + convert(varchar(10), @p_date, 101)
		set @p_message = '<Response>' 
					+ '<Message type=''' + @w_msgType + '''><Text>'
					+ @w_message
					+ '</Text></Message></Response>'
		return;
	end

-- Can this be submitted?
select @w_active_count = count(*) from V_TimeKeeping_DATE_ACT_STS_HOURS ad WHERE ad.EH_DATE_WORKED = @p_date AND ad.Total_Hours > 0 AND ad.EH_RECORDING_STATUS = 'A'

if @w_active_count > 0
	begin
		set @w_message = 'All recorded time activity must be marked as Submitted'
		set @p_message = '<Response>' 
					+ '<Message type=''' + @w_msgType + '''><Text>'
					+ @w_message
					+ '</Text></Message></Response>'
		return;
	end

select @w_submitted_count = count(*) from V_TimeKeeping_DATE_ACT_STS_HOURS ad WHERE ad.EH_DATE_WORKED = @p_date AND ad.Total_Hours > 0 AND ad.EH_RECORDING_STATUS = 'S'

if @w_submitted_count = 0
	begin
		set @w_message = 'There is no recorded activity for this date' + convert(varchar(10), @p_date, 101)
		set @p_message = '<Response>' 
					+ '<Message type=''' + @w_msgType + '''><Text>'
					+ @w_message
					+ '</Text></Message></Response>'
		return;
	end

GO

INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 3, N'Cancel', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 2, N'Back', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 4, N'Confirm Employee Name', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 5, N'Continue', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 6, N'Edit Timesheet', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 7, N'Employee Hours by Date/Activity', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 8, N'Enter Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 9, N'Last Friday''s Hours (Mon only)', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 10, N'Menu', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 11, N'Record Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 1, N'Activity Comment', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 12, N'Reset Activity', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 13, N'Submit Timesheet', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 14, N'Timekeeping Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 15, N'Timekeeping Hours: By Activity', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 16, N'Today', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 17, N'Today Activity Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 18, N'Today Total Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 19, N'Today''s Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 20, N'Total Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 21, N'Worked Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 22, N'Yesterday''s Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 1, N'Actividad Comentario', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 2, N'Espalda', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 3, N'Cancelar', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 4, N'Confirmar el nombre del empleado', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 5, N'Continuar', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 6, N'Editar parte de horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 7, N'Horas del empleado por fecha / actividad', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 8, N'Ingrese Horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 9, N'Horas del último viernes (solo el lunes)', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 10, N'Menú', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 11, N'Horas record', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 12, N'Restablecer actividad', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 13, N'Parte de horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 14, N'Horario de hora normal', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 15, N'Horario de la hora normal: por actividad', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 16, N'Hoy', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 17, N'Horario de actividad de hoy', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 18, N'Horas totales de hoy', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 19, N'Horas de hoy', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 20, N'Horas Totales', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 21, N'Horas Trabajadas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 22, N'Horas de ayer', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 1, N'Comentário de atividade', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 2, N'Costas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 3, N'Cancelar', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 4, N'Confirme o nome do funcionário', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 5, N'Continuar', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 6, N'Editar quadro de horários', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 7, N'Horas do empregado por data / atividade', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 8, N'Digite as horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 9, N'Horário da última sexta-feira (somente seg)', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 10, N'Cardápio', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 11, N'Gravar horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 12, N'Redefinir Atividade', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 13, N'Enviar quadro de horários', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 14, N'Horas de cronometragem', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 15, N'Horas de cronometragem: por atividade', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 16, N'Hoje', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 17, N'Horas de atividade hoje', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 18, N'Total de horas hoje', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 19, N'Horas de hoje', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 20, N'Total de horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 21, N'Horas trabalhadas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 22, N'Horas de ontem', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 23, N'Employees', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 23, N'Empleados', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 23, N'Funcionários', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 24, N'Languages', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 24, N'Idiomas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 24, N'línguas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 25, N'By Activity', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 25, N'Por actividad', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 25, N'Por atividade', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 26, N'Activity', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 26, N'Actividad', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 26, N'Atividade', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 27, N'Status', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 27, N'Estado', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 27, N'Status', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 28, N'Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 28, N'Horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 28, N'Horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 29, N'Yesterday', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 29, N'Ayer', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 29, N'Ontem', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 30, N'Active', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 30, N'Activo', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 30, N'Ativo', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 31, N'Submitted', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 31, N'Presentada', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 31, N'Submetido', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 32, N'Support', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 32, N'Apoyo', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 32, N'Suporte', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 33, N'Last Friday', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 33, N'El viernes Pasado', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 33, N'Última Sexta-feira', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 34, N'New', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 34, N'Nuevo', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 34, N'Novo', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 35, N'Yesterday Total Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 35, N'Horas Totales de ayer', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 35, N'Ontem Total de Horas', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 36, N'Yesterday Activity Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 36, N'Horas de actividad de ayer', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 36, N'Ontem Horas de Atividade', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 37, N'Friday Total Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 37, N'Viernes Horas Totales', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 37, N'Total de horas de sexta-feira', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 38, N'Friday Activity Hours', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 38, N'Horario de actividad los viernes', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 38, N'Horário de atividade de sexta-feira', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 39, N'Reset was not done. Timesheet already processed.', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 39, N'El reinicio no se hizo. Hoja de tiempo ya procesada.', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 39, N'Reset não foi feito. Quadro de horários já processado.', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 40, N'Hours worked should be with 0.25 increments. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 40, N'Las horas trabajadas deben ser con incrementos de 0.25. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 40, N'Horas trabalhadas devem ser com incrementos de 0,25. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 41, N'Employee cannot work more than 24 hours per day. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 41, N' El empleado no puede trabajar más de 24 horas por día. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 41, N' O funcionário não pode trabalhar mais de 24 horas por dia. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 42, N'Hours worked should be greater than 0. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 42, N'Las horas trabajadas deben ser mayores que 0. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 42, N'Horas trabalhadas devem ser maiores que 0. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 43, N'Timesheet already submitted. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 43, N'Hoja de tiempo ya enviada. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 43, N'Quadro de horários já enviado. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 44, N'Timesheet does not have any hour. Please update and submit again. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 44, N'El parte de horas no tiene ninguna hora. Por favor actualice y envíe nuevamente. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 44, N'Quadro de horários não tem hora. Por favor, atualize e envie novamente. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 45, N'Day already submitted. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 45, N'Día ya enviado. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 45, N'Dia já enviado. ', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'en-US', 46, N'You must enter an Activity Code', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'es-ES', 46, N'Debes ingresar un código de actividad', null);
INSERT INTO dbo.LanguageLabels(LanguageID, LabelID, LabelContent, UpdateDate) VALUES (N'pt-BR', 46, N'Você deve inserir um código de atividade', null);

